package com.example.springbooteasycode.zjrc.project.Other;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

public class ExcelGenerator {
    public static void main(String[] args) {
        // javaAIX
//        javaAIX();
        // cSMPouterAIX
//        cSMPouterAIX();
        // cSMPinetfAIX
//        cSMPinetfAIX();
        // sumToExcel
        sumToExcel();
    }


    /**
     * 汇总 每个指令的 交易量总和
     */
    public static void sumToExcel() {
        String filePath = "/Users/boykchen/Desktop/生产指令交易量统计.xlsx"; // 原始 Excel 文件路径
        String sheetName = "CSMPinetf-2025"; // 指定的工作表名称

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(filePath))) {
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                System.out.println("工作表不存在！");
                return;
            }

            // 读取原始数据
            Map<String, Integer> summaryMap = new HashMap<>();
            int rowCount = sheet.getPhysicalNumberOfRows();

            for (int i = 1; i < rowCount; i++) { // 假设表头在第一行，数据从第二行开始
                Row row = sheet.getRow(i);
                if (row == null) continue;

                Cell volumeCell = row.getCell(0); // 交易量在第一列（A列）
                Cell commandCell = row.getCell(1); // 指令在第二列（B列）

                if (volumeCell == null || commandCell == null) continue;

                // 获取交易量和指令值
                int volume = (int) volumeCell.getNumericCellValue();
//                String command = commandCell.getStringCellValue();
                String command;

                // 处理指令单元格的不同类型
                if (commandCell.getCellType() == CellType.STRING) {
                    command = commandCell.getStringCellValue();
                } else if (commandCell.getCellType() == CellType.NUMERIC) {
                    command = String.valueOf((int) commandCell.getNumericCellValue());
                } else {
                    command = "";
                }

                // 按指令汇总交易量
                summaryMap.put(command, summaryMap.getOrDefault(command, 0) + volume);
            }

            // 创建新的工作表用于存储汇总结果
            Sheet summarySheet = workbook.createSheet("汇总结果");

            // 创建表头
            Row headerRow = summarySheet.createRow(0);
            headerRow.createCell(0).setCellValue("指令");
            headerRow.createCell(1).setCellValue("汇总交易量");

            // 填充汇总数据
            int rowIndex = 1;
            for (Map.Entry<String, Integer> entry : summaryMap.entrySet()) {
                Row row = summarySheet.createRow(rowIndex++);
                row.createCell(0).setCellValue(entry.getKey());
                row.createCell(1).setCellValue(entry.getValue());
            }

            // 自动调整列宽
            for (int i = 0; i < 2; i++) {
                summarySheet.autoSizeColumn(i);
            }

            // 保存修改后的 Excel 文件
            try (FileOutputStream fileOut = new FileOutputStream("/Users/boykchen/Desktop/交易量汇总结果.xlsx")) {
                workbook.write(fileOut);
                System.out.println("汇总结果已保存到交易量汇总结果.xlsx");
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



    /**
     * CSMPinetf-AIX
     */
    public static void cSMPinetfAIX() {
        // 创建一个新的工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("交易量和指令汇总");

        // 创建表头行
        Row headerRow = sheet.createRow(0);
        Cell headerCell1 = headerRow.createCell(0);
        headerCell1.setCellValue("交易量");
        Cell headerCell2 = headerRow.createCell(1);
        headerCell2.setCellValue("指令");

        // 定义数据数组，左边是交易量，右边是指令
        Object[][] data = {
                {1804, "04"},
                {3156, "05"},
                {23128, "07"},
                {44640, "03"},
                {483590, "01"},
                {11118, "04"},
                {2061, "05"},
                {21929, "07"},
                {40320, "03"},
                {482006, "01"},
                {1623, "04"},
                {3042, "05"},
                {22331, "07"},
                {44640, "03"},
                {534330, "01"},
                {6, "00"},
                {1480, "04"},
                {1995, "05"},
                {17506, "07"},
                {43088, "03"},
                {479532, "01"},
                {1235, "04"},
                {1342, "05"},
                {14417, "07"},
                {44640, "03"},
                {497950, "01"},
                {1277, "04"},
                {1327, "05"},
                {15535, "07"},
                {43200, "03"},
                {481610, "01"}
        };

        // 填充数据
        for (int i = 0; i < data.length; i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < data[i].length; j++) {
                Cell cell = row.createCell(j);
                if (data[i][j] instanceof String) {
                    cell.setCellValue((String) data[i][j]);
                } else if (data[i][j] instanceof Integer) {
                    cell.setCellValue((Integer) data[i][j]);
                }
            }
        }

        // 自动调整列宽以适应内容
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);

        // 保存 Excel 文件
        try (FileOutputStream fileOut = new FileOutputStream("/Users/boykchen/Desktop/交易量和指令汇总-cSMPinetfAIX.xlsx")) {
            workbook.write(fileOut);
            System.out.println("Excel 文件已成功生成！");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     *CSMPouter-AIX
     */
    public static void cSMPouterAIX() {
        // 创建一个新的工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        Sheet sheet = workbook.createSheet("交易量和指令汇总");

        // 创建表头行
        Row headerRow = sheet.createRow(0);
        Cell headerCell1 = headerRow.createCell(0);
        headerCell1.setCellValue("交易量");
        Cell headerCell2 = headerRow.createCell(1);
        headerCell2.setCellValue("指令");

        // 定义数据数组，左边是交易量，右边是指令
        Object[][] data = {
                {44640, "03"},
                {44640, "03"},
                {40320, "03"},
                {44640, "03"},
                {3, ""},
                {16, "04"},
                {43088, "03"},
                {44640, "03"},
                {43200, "03"}
        };

        // 填充数据
        for (int i = 0; i < data.length; i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < data[i].length; j++) {
                Cell cell = row.createCell(j);
                if (data[i][j] instanceof String) {
                    cell.setCellValue((String) data[i][j]);
                } else if (data[i][j] instanceof Integer) {
                    cell.setCellValue((Integer) data[i][j]);
                }
            }
        }

        // 自动调整列宽以适应内容
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);

        // 保存 Excel 文件
        try (FileOutputStream fileOut = new FileOutputStream("/Users/boykchen/Desktop/交易量和指令汇总-cSMPouterAIX.xlsx")) {
            workbook.write(fileOut);
            System.out.println("Excel 文件已成功生成！");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Java-AIX
     */
    public static void javaAIX() {
        // 创建新的工作簿和工作表
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("交易量汇总");

        // 创建表头行
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("交易量");
        headerRow.createCell(1).setCellValue("指令");

        // 数据
        Object[][] data = {
                {44649, "03"},
                {122625, "01"},
                {345257, "02"},
                {40334, "03"},
                {97425, "01"},
                {291900, "02"},
                {44660, "03"},
                {102825, "01"},
                {366343, "02"},
                {6, "00"},
                {43101, "03"},
                {93109, "01"},
                {347350, "02"},
                {44655, "03"},
                {96605, "01"},
                {309833, "02"},
                {43208, "03"},
                {93480, "01"},
                {326926, "02"}
        };

        // 填充数据
        for (int i = 0; i < data.length; i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < data[i].length; j++) {
                Cell cell = row.createCell(j);
                if (data[i][j] instanceof String) {
                    cell.setCellValue((String) data[i][j]);
                } else if (data[i][j] instanceof Integer) {
                    cell.setCellValue((Integer) data[i][j]);
                }
            }
        }

        // 自动调整列宽
        for (int i = 0; i < 2; i++) {
            sheet.autoSizeColumn(i);
        }

        // 保存文件
        try (FileOutputStream fileOut = new FileOutputStream("/Users/boykchen/Desktop/交易量_指令_汇总-java-AIX.xlsx")) {
            workbook.write(fileOut);
            System.out.println("Excel文件已成功生成！");
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

